import xlwings as xw
import pandas as pd
from pathlib import Path
import shutil
from openpyxl import Workbook, load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows


this_dir = Path(__file__).resolve().parent

def get_subdir(floder):
    subfs = (this_dir / floder).glob('**/')
    return [sub for sub in subfs][1:]

def get_csv_paths(floder:str):
    return [path for path in (this_dir / floder).rglob('*.csv')]

def get_fnames(paths:Path):
    return [path.name for path in paths]

def create_summary_excel(src:str, dst:str):
    shutil.copy2(src, dst)

def merge(paths:list[Path], output_xl:str):
    parts = []
    for path in paths:
        part = pd.read_csv(path)
        part['A'] = pd.NA
        part['B'] = pd.NA
        part['from'] = path.name
        parts.append(part)
    df = pd.concat(parts)
    df = df.reset_index(drop=True)
    df.index += 2
    df.to_excel(output_xl, index=False)
    # 创建一个新的workbook
    wb = load_workbook(output_xl)
    # 选择我们要操作的工作表
    ws = wb.active   
    
    # 假设我们要在C列插入一列公式，例如C1等于A1 + B1
    for idx, row in df.iterrows():
        ws[f'AF{idx}'] = f'=(2*I{idx}*(1-I{idx})*G{idx}^2)/((2*I{idx}*(1-I{idx})*G{idx}^2)+(2*I{idx}*(1-I{idx})*X{idx}*V{idx}^2))'  # Excel的公式就是这样的文本
        ws[f'AG{idx}'] = f'=(AF{idx}*(X{idx}-2))/(1-AF{idx})'
    wb.save(output_xl)
    print(f'generate {output_xl}')

def create_summerize(sub, datasrc, dst):
    try:
        app = xw.App(visible=False, add_book=False)
        wb = app.books.open(sub / datasrc)
        df = wb.sheets[0]['A1'].expand().options(pd.DataFrame).value
        wb.close()
        app.quit()
        stat = (
            df
            .groupby('from')
            .agg({
                'A': 'sum',
                'B': ['mean', 'min', 'max']
            })
        )
        stat.columns = ['a值总和', 'b值平均值', 'b值最小值', 'b值最大值']
        stat.index.name = 'id'
        stat.to_excel(dst)
        app = xw.App(visible=False, add_book=False)
        wb =  app.books.open(dst)
        st = wb.sheets[0]
        st.autofit()
        wb.save()
 
        print(f'created {dst}.') 
    except Exception as e:
        print(f'failed:{e}')  
    finally:
        wb.close()
        app.quit()    
